/***********************************************************************************************************************************
 *    Name: Permissions_Audit.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Lists all user permissions in the current database and generates the T-SQL statement to apply those permissions.
**/

-- Role Membership
WITH m (role_id, member_name, member_type) AS (SELECT role_principal_id, name, type_desc FROM sys.database_role_members rm INNER JOIN sys.database_principals m ON (rm.member_principal_id = m.principal_id))
SELECT
	[Role Name]		= r.name,
	[Role Type]		= r.type,
	[Member Name]	= m.member_name,
	[Member Type]	= m.member_type
  FROM sys.database_principals r LEFT JOIN m ON (r.principal_id = m.role_id)
  WHERE r.type IN ('A','R');
GO

-- Permissions
WITH p (Action, Permission, ObjectName, GType, Grantee, Grantor) AS (SELECT
	w.state_desc,
	w.permission_name,
	CASE w.class
		WHEN  0 THEN DB_NAME()																						-- Database
		WHEN  1 THEN SCHEMA_NAME(o.schema_id) + N'.' + OBJECT_NAME(w.major_id) + ISNULL(N'(' + c.name + N')', N'')	-- Object or Column
		WHEN  3 THEN SCHEMA_NAME(w.major_id)																		-- Schema
		WHEN  4 THEN (SELECT name FROM sys.database_principals WHERE principal_id = w.major_id)						-- Database Principal
		WHEN  5 THEN (SELECT clr_name COLLATE DATABASE_DEFAULT FROM sys.assemblies WHERE assembly_id = w.major_id)	-- Assembly
		WHEN  6 THEN TYPE_NAME(w.major_id)																			-- Type
		WHEN 10 THEN (SELECT name FROM sys.xml_schema_collections WHERE xml_collection_id = w.major_id)				-- XML Schema Collection
		WHEN 15 THEN (SELECT name FROM sys.service_message_types WHERE message_type_id = w.major_id)				-- Message Type
		WHEN 16 THEN (SELECT name FROM sys.service_contracts WHERE service_contract_id = w.major_id)				-- Service Contract
		WHEN 17 THEN (SELECT name FROM sys.services WHERE service_id = w.major_id)									-- Service
		WHEN 18 THEN (SELECT name FROM sys.remote_service_bindings WHERE remote_service_binding_id = w.major_id)	-- Remote Service Binding
		WHEN 19 THEN (SELECT name FROM sys.routes WHERE route_id = w.major_id)										-- Route
		WHEN 23 THEN (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id = w.major_id)				-- Full-Text Catalog
		WHEN 24 THEN (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id = w.major_id)						-- Symmetric Key
		WHEN 25 THEN (SELECT name FROM sys.certificates WHERE certificate_id = w.major_id)							-- Certificate
		WHEN 26 THEN (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id = w.major_id)					-- Asymmetric Key
		ELSE CAST(w.major_id AS NVARCHAR(10))
	END	AS ObjectName,
	t.type,
	t.name,
	a.name
  FROM sys.database_permissions w
	INNER JOIN sys.database_principals t ON (w.grantee_principal_id = t.principal_id)
	INNER JOIN sys.database_principals a ON (w.grantor_principal_id = a.principal_id)
	LEFT OUTER JOIN sys.objects o ON (w.major_id = o.object_id)
	LEFT OUTER JOIN sys.columns c ON (w.major_id = o.object_id AND w.minor_id = c.column_id)
  WHERE 0 <= w.major_id	-- exclude system objects
	)
SELECT DBName= DB_NAME(), Action, Permission, ObjectName, Grantee, Grantor,
	[T-SQL Grant]	= Action + SPACE(1) + Permission + N' ON [' + REPLACE(ObjectName, N'.', N'].[') + N'] TO [' + Grantee + N'] AS [' + Grantor + N'];',
	[T-SQL Revoke]	= N'REVOKE ' + Permission + N' ON [' + REPLACE(ObjectName, N'.', N'].[') + N'] FROM [' + Grantee + N'] AS [' + Grantor + N'];'
  FROM p
--WHERE 
  ORDER BY Grantee, ObjectName, GType, Permission;
GO
